package model;

import java.sql.*;
import java.util.Vector;

import javax.naming.NamingException;
import javax.sql.rowset.CachedRowSet;

import com.sun.rowset.CachedRowSetImpl;

import db.DbConnectionPool;
import db.DbException;
import forms.ApplicantFormInsert;
import forms.ReviewerFormInsert;
import forms.ReviewerFormDelete;

public class ReviewerModel {
	
	private static String getReviewer = "SELECT id, name FROM Reviewers ORDER BY id";
	private static String countReviewer = "SELECT count(id) FROM Reviewers";
	private static String addReviewer = "INSERT INTO Reviewers (name,password) VALUES (?,md5(?))";
	private static String deleteStr = "DELETE FROM Reviewers WHERE id = ?";
	

	
	public static CachedRowSet getReviewer() throws DbException{
		try {
			Connection conn = DbConnectionPool.getConnection();
			conn.setAutoCommit(false);
			PreparedStatement pstmt =conn.prepareStatement(getReviewer);
			
			
			ResultSet rs = pstmt.executeQuery();
	

			CachedRowSet crsReviewers = new CachedRowSetImpl();
			crsReviewers.populate(rs);
	   
			
			pstmt.close();
            conn.setAutoCommit(true);
            conn.close();
            
            return crsReviewers;
            
            
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	
	public static void addReviewer(ReviewerFormInsert reviewer) throws DbException{
		try {
			Connection conn = DbConnectionPool.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(addReviewer);
			
			pstmt.setString(1,reviewer.getName());
			pstmt.setString(2,reviewer.getPassword());
			pstmt.executeUpdate();  
			
			pstmt.close();
            conn.setAutoCommit(true);
            conn.close();

            
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	
	public static void deleteReviewer(ReviewerFormDelete reviewer)	throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			PreparedStatement pStmt = conn.prepareStatement(deleteStr);

			pStmt.setInt(1, reviewer.getId());
			pStmt.executeUpdate();

			// Commit transaction
			conn.commit();

			pStmt.close();
			conn.close();
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}


	
	
	
	public static int getReviewerCount() throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pstmt = conn.prepareStatement(countReviewer);

			
			ResultSet rs = pstmt.executeQuery();
			rs.next();
			int count = rs.getInt(1);
	
			pstmt.close();
			conn.close();

			return count;
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}

	/*
        public static void distributeReviewers() throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(countStr);

			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			ResultSet allStudents = pStmt.executeQuery();

			CachedRowSet reviewers = new CachedRowSetImpl();
			reviewers.populate(allStudents);

			allStudents.close();
			pStmt.close();
			conn.close();

			return count;
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}

	*/

	
	
	
	
	
	
	
}

